<?

//Data loading
$title='REKAP UMUR PIUTANG';
if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));
else $perush = 'PT. MASAJI PRAYASA CARGO';

$page   = 'PAGE';

$DEFINE_FONT=0;
$pdf=new PDFReport('L',"mm","Letter");
$pdf->AddPage();
$Result = GridList ();
if ($Result) $pdf->GridTable($Result);

$pdf->Output();

function GetFromDate () {
//  list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,GetParam("m",""),1,GetParam("y",""))));
    return array($Day,$Month,$Year,$NMonth);
}

function GetLastPeriod ($Month, $Year) {
    list($LDay,$LMonth,$LYear,$LNMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,$Month,0,$Year)));
    return array($LMonth,$LYear,$LNMonth);
}

function FlushCustomer ($TableName, $tmp) {
    global $DBConnection,$DBName,$DBGeneral;

    if ($tmp) $TEMPORARY = "TEMPORARY";

    //$SQL = "DROP TABLE IF EXISTS `$TableName`;";
    //$DBConnection->dbc->query($SQL);

    $SQL= "CREATE $TEMPORARY TABLE `$TableName` (
              `cucode` varchar(20) NOT NULL default '',
              `cunama` varchar(50) default NULL,
              `top` int(4) default NULL

              )";
    $DBConnection->dbc->query($SQL);
    $SQL = "INSERT INTO $DBName.`$TableName` SELECT cucode,cuname,top FROM tbldebtur ";
    $DBConnection->dbc->query($SQL);

    return true;
}

function GridList () {
    global $DBConnection;
    $TEMP = "TEMPORARY";

    if (!FlushCustomer ("ais_customer", 1)) return false;
    //$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));

    list($day,$month,$year) = explode("/",GetParam("agingDate",""));
    $current_date = "$year-$month-$day";
    $LastDate  = GetLastDateReport();
    //$SQL = "DROP TABLE IF EXISTS `ais_piutang_terhutang`";
    //$DBConnection->dbc->query($SQL);

    $SQL = "
        CREATE $TEMP TABLE `ais_piutang_terhutang` (
      `ktrasl` varchar(5) default NULL,
      `tglbkt` date,
      `nobkt` varchar(16) default NULL,
           `nobkta` varchar(16) default NULL,
                      `debtur` varchar(16) default NULL,
                  `debnam` varchar(32) default NULL,
                      `uraian` text default NULL,
                      `curr` varchar(4) default NULL,
                      `jumlah` decimal(15,2) default NULL,
                      `top` tinyint(4)default NULL,
            `top_curr` date,
            `top_30` date,
            `top_60` date,
            `aging` varchar(16)
                    );
                   ";
    $DBConnection->dbc->query($SQL);

    $SQL = "
                    INSERT INTO ais_piutang_terhutang
                    SELECT KTRASL, TGLBKT, NOBKT,NOBKTA, tbldebtur.cucode, tbldebtur.cuname, URAIAN, CURR, JUMLAH, IFNULL(top,0) AS TOP,

                    ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ) as top_curr,
                    ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ) as top_30,
                    ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ) as top_60,

                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ),'0',
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ),'30',
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ),'60','60g')))

                    FROM ais_piutang
                    LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
                    WHERE srt='1' AND ais_piutang.tglbkt<='$current_date'
                   ";

    if (strlen($LastDate))
        $SQL .= "AND ais_piutang.tglbkt>'$LastDate' ";

    if (GetParam("fromSkada",""))
        $SQL .= "AND ais_piutang.skada >= '".GetParam("fromSkada","")."' ";

    if (GetParam("curr",""))
        $SQL .= "AND ais_piutang.curr = '".GetParam("curr","")."' ";

    if (GetParam("toSkada",""))
        $SQL .= "AND ais_piutang.skada <= '".GetParam("toSkada","")."' ";

        //print $SQL;die();
    if (GetParam("office",""))  $SQL .= "AND ais_piutang.ktrasl = '".GetParam("office","")."' ";
    $SQL .= "order by nobkt ";
            //die($SQL);
    $DBConnection->dbc->query($SQL);

    //$SQL = "DROP TABLE IF EXISTS `ais_piutang_terbayar`";
    //$DBConnection->dbc->query($SQL);

    $SQL = "
                    CREATE $TEMP TABLE `ais_piutang_terbayar` (
                      `nobkt` varchar(16) default NULL,
                      `nobkta` varchar(16) default NULL,
                      `bayar` decimal(15,2) default NULL,
                      `debtur` varchar(64) default NULL,
                      `debnam` varchar(128) default NULL
                    ) ;
                   ";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    INSERT INTO ais_piutang_terbayar
                    SELECT NOBKT, NOBKTA, SUM(JUMLAH),debtur,debnam
                    FROM ais_piutang
                    LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
                    WHERE srt='2' AND ais_piutang.tglbkt<='$current_date' ";

    if (strlen($LastDate))
        $SQL .= "AND ais_piutang.tglbkt>'$LastDate' ";

                        //print $SQL;die();
            if (GetParam("office",""))
        $SQL .= "AND ais_piutang.ktrasl = '".GetParam("office","")."' ";

    if (GetParam("curr",""))
        $SQL .= "AND ais_piutang.curr = '".GetParam("curr","")."' ";

        $SQL .= "group by nobkta order by nobkta";
                        //print $SQL;die();
    $DBConnection->dbc->query($SQL);
            //$DBConnection->dbc->debug();

//Auto Update Using Cache Begin Here
     if (strlen($LastDate)) {

    //$SQL = "DROP TABLE IF EXISTS `ais_piutang_sisa_period`";
                //$DBConnection->dbc->query($SQL);

        $SQL = "
                        CREATE $TEMP TABLE `ais_piutang_sisa_period` (
                          `ktrasl` varchar(5) default NULL,
                          `tglbkt` date,
                          `aging_date` date,
                          `nobkt` varchar(16) default NULL,
                          `debtur` varchar(16) default NULL,
                          `debnam` varchar(32) default NULL,
                          `uraian` text default NULL,
                          `top` tinyint(4)default NULL,
                          `jumlah` decimal(15,2) default NULL,
                          `aging` varchar(16),
                          `fromskada` varchar(16),
                          `toskada` varchar(16),
                          `curr`varchar(5)
                        )
                       ";
        $DBConnection->dbc->query($SQL);
        $SQL = "
                          INSERT INTO ais_piutang_sisa_period
                          SELECT *
                          FROM ais_piutang_sisa
                          WHERE aging_date='$LastDate' AND fromskada='".GetParam("fromSkada","")."' AND toskada='".GetParam("toSkada","")."'
                        ";
            if (GetParam("curr",""))  $SQL .= " AND curr='".GetParam("curr","")."' ";
                     //die($SQL);
        $DBConnection->dbc->query($SQL);

        $SQL = "
                          INSERT INTO ais_piutang_sisa_period
                          SELECT ktrasl,tglbkt,'$current_date',nobkt,debtur,debnam,uraian,top,jumlah,aging,'".GetParam("fromSkada","")."','".GetParam("toSkada","")."','".GetParam("curr","")."'
                          FROM ais_piutang_terhutang
                        ";
        $DBConnection->dbc->query($SQL);

        if (isTimeout(45,$current_date,$LastDate)) {
            $SQL = "
                              INSERT INTO ais_piutang_sisa
                              SELECT
                              ais_piutang_sisa_period.ktrasl,
                              ais_piutang_sisa_period.tglbkt,
                              '$current_date',
                              ais_piutang_sisa_period.nobkt,
                              ais_piutang_sisa_period.debtur,
                              ais_piutang_sisa_period.debnam,
                              ais_piutang_sisa_period.uraian,
                              ifnull(ais_piutang_sisa_period.top, 0) AS top,
                              ifnull(ais_piutang_sisa_period.jumlah,0)-ifnull(ais_piutang_terbayar.bayar,0),

                              IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_piutang_sisa_period.top DAY ),'0',
                              IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_piutang_sisa_period.top DAY ),'30',
                              IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_piutang_sisa_period.top DAY ),'60','60g'))),
                              '".GetParam("fromSkada","")."','".GetParam("toSkada","")."','".GetParam("curr","")."'

                              FROM
                              ais_piutang_sisa_period
                              LEFT JOIN ais_piutang_terbayar ON (ais_piutang_sisa_period.nobkt = ais_piutang_terbayar.nobkta)
                              WHERE (ifnull(ais_piutang_terbayar.nobkt, '') = '') OR ifnull(ais_piutang_sisa_period.jumlah-ais_piutang_terbayar.bayar,0) <> 0
                              ORDER BY debtur,nobkt
                            ";
                     $DBConnection->dbc->query($SQL);
//die("TimeOut".$SQL);
        } else { // isTimeOut
            //$SQL = "DROP TABLE IF EXISTS `ais_piutang_sisa_notimeout`";
            //$DBConnection->dbc->query($SQL);
                    //$DBConnection->dbc->debug();
                $SQL = "
                        CREATE $TEMP TABLE `ais_piutang_sisa_notimeout`
                        (
                        `ktrasl` varchar(5) default NULL,
                        `tglbkt` date default NULL,
                        `aging_date` date default NULL,
                        `nobkt` varchar(16) default NULL,
                        `debtur` varchar(16) default NULL,
                        `debnam` varchar(32) default NULL,
                        `uraian` text,
                        `cr_term` tinyint(4) default NULL,
                        `jumlah` decimal(15,2) default NULL,
                        `aging` varchar(16) default NULL,
                        `fromskada` varchar(64) default NULL,
                        `toskada` varchar(64) default NULL,
                        `curr` varchar(6) NOT NULL default ''
                        )
                    ";
//die("create NotimeOut".$SQL);
            $DBConnection->dbc->query($SQL);
                    //$DBConnection->dbc->debug();

            $SQL = "
                              INSERT INTO ais_piutang_sisa_notimeout
                              SELECT
                              ais_piutang_sisa_period.ktrasl,
                              ais_piutang_sisa_period.tglbkt,
                              '$current_date',
                              ais_piutang_sisa_period.nobkt,
                              ais_piutang_sisa_period.debtur,
                      ais_piutang_sisa_period.debnam,
                              ais_piutang_sisa_period.uraian,
                              ifnull(ais_piutang_sisa_period.top, 0) AS top,
                              ifnull(ais_piutang_sisa_period.jumlah,0)-ifnull(ais_piutang_terbayar.bayar,0),

                              IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_piutang_sisa_period.top DAY ),'0',
                              IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_piutang_sisa_period.top DAY ),'30',
                              IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_piutang_sisa_period.top DAY ),'60','60g'))),
                              '".GetParam("fromSkada","")."','".GetParam("toSkada","")."','".GetParam("curr","")."'

                              FROM
                              ais_piutang_sisa_period
                              LEFT JOIN ais_piutang_terbayar ON (ais_piutang_sisa_period.nobkt = ais_piutang_terbayar.nobkta)
                              WHERE (ifnull(ais_piutang_terbayar.nobkt, '') = '') OR ifnull(ais_piutang_sisa_period.jumlah,0)-ifnull(ais_piutang_terbayar.bayar,0) <> 0
                              ORDER BY debtur,nobkt
                            ";

                     $DBConnection->dbc->query($SQL);
//die("NoTimeOut".$SQL);
        } // is TimeOut

    } else { // lastdate
                     $SQL = "
                              INSERT INTO ais_piutang_sisa
                              SELECT
                              ais_piutang_terhutang.ktrasl,
                              ais_piutang_terhutang.tglbkt,
                              '$current_date',
                              ais_piutang_terhutang.nobkt,
                              ais_piutang_terhutang.debtur,
                              ais_piutang_terhutang.debnam,
                              ais_piutang_terhutang.uraian,
                              ifnull(ais_piutang_terhutang.top, 0) AS top,
                              ifnull(ais_piutang_terhutang.jumlah,0)-ifnull(ais_piutang_terbayar.bayar,0),

                                IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_piutang_terhutang.top DAY ),'0',
                                IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_piutang_terhutang.top DAY ),'30',
                                IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_piutang_terhutang.top DAY ),'60','60g'))),
                                '".GetParam("fromSkada","")."','".GetParam("toSkada","")."','".GetParam("curr","")."'

                              FROM
                              ais_piutang_terhutang
                              LEFT JOIN ais_piutang_terbayar ON (ais_piutang_terhutang.nobkt = ais_piutang_terbayar.nobkta)
                              WHERE (ifnull(ais_piutang_terbayar.nobkt, '') = '') OR ifnull(ais_piutang_terhutang.jumlah-ais_piutang_terbayar.bayar,0) <> 0
                              ORDER BY debtur,nobkt
                            ";
                    $DBConnection->dbc->query($SQL);

    } // lastdate

//Auto Update Using Cache End Here

//  $SQL = "SELECT * FROM ais_aging WHERE aging_date='$current_date'";
    if (isTimeout(45,$current_date,$LastDate))
        $TableName = "ais_piutang_sisa ";
    else
        $TableName = "ais_piutang_sisa_notimeout ";

    $SQL = "
                SELECT ktrasl, nobkt, debtur, debnam, uraian, cr_term as top,
                SUM(if($TableName.aging='0',jumlah,0)) AS jumlah,curr,
                SUM(if($TableName.aging='30',jumlah,0)) AS jumlah30,
                SUM(if($TableName.aging='60',jumlah,0)) AS jumlah60,
                SUM(if($TableName.aging='60g',jumlah,0)) AS jumlah60g
                FROM $TableName
                WHERE aging_date='$current_date' AND

                fromskada='".GetParam("fromSkada","")."' AND
                toskada='".GetParam("toSkada","")."' AND
                curr='".GetParam("curr","")."'

                GROUP BY debtur
                ORDER BY debtur
            ";
//die($SQL);
    return $DBConnection->dbc->get_results($SQL, ARRAY_A);
}

function isTimeout($day,$current_date,$LastDate) {
    global $DBConnection;
    if (!$LastDate) return 1;
    $SQL = "SELECT IF(ADDDATE('$LastDate', INTERVAL $day DAY)<='$current_date',1,0)";
    //print $SQL;die();
    return $DBConnection->dbc->get_var($SQL);
}

function ReadCOA($no) {
            $DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
            $SQL = "SELECT namacc FROM tblcoa WHERE skada='".$no."'";
            $MyResult = $DBConnection->dbc->get_var($SQL);
            return $MyResult;
}

function GetLastDateReport() {
        global $DBConnection;
        list($day,$month,$year) = explode("/",GetParam("agingDate",""));
        $current_date = "$year-$month-$day";

        $SQL = "DELETE FROM ais_piutang_sisa WHERE aging_date>='$current_date' ";
        if (GetParam("curr","")) $SQL .= " AND curr='".GetParam("curr","")."' ";
        if (GetParam("fromSkada","")) $SQL .= " AND fromskada = '".GetParam("fromSkada","")."' ";
        if (GetParam("toSkada","")) $SQL .= " AND toskada = '".GetParam("toSkada","")."' ";
        $DBConnection->dbc->query($SQL);
        //$DBConnection->dbc->debug();

        $SQL = "SELECT max(aging_date) FROM ais_piutang_sisa WHERE aging_date<='$current_date' ";
        if (GetParam("curr","")) $SQL .= " AND curr='".GetParam("curr","")."' ";
        if (GetParam("fromSkada","")) $SQL .= " AND fromskada = '".GetParam("fromSkada","")."' ";
        if (GetParam("toSkada","")) $SQL .= " AND toskada = '".GetParam("toSkada","")."' ";

        return $DBConnection->dbc->get_var($SQL);
}

class PDFReport extends PDF {

    function Header() {
        global $title,$perush,$page,$DEFINE_FONT;
        if ($DEFINE_FONT==0) {
            $this->AddFont('Arial','','arialn.php');
            $DEFINE_FONT=1;
        }

        list($Day,$Month,$Year,$NMonth) = GetFromDate();
        list($LMonth,$LYear,$LNMonth) = GetLastPeriod ($Month, $Year);

        $this->SetFont('Arial','',10);
        $this->Cell(260,5,$perush,0,1,'C',0);
        $this->Cell(260,5,$title,0,1,'C',0);
        $this->Cell(260,5,"AS OF: ".GetParam("agingDate",""),0,1,'C',0);

/*
        if (GetParam("fromSkada",""))
           $this->Cell(260,5,"FROM SKADA: ".GetParam("fromSkada","")." - ".ReadCOA(GetParam("fromSkada","")),0,1,'L',0);
        else
           $this->Cell(260,5,"FROM SKADA: ALL",0,1,'L',0);
        if (GetParam("toSkada",""))
           $this->Cell(260,5,"TO SKADA: ".GetParam("toSkada","")." - ".ReadCOA(GetParam("toSkada","")),0,1,'L',0);
        else
        $this->Cell(260,5,"TO SKADA: ALL",0,1,'L',0);
*/
      $this->Cell(260,5,"SKADA: ".GetParam("fromSkada","")." - ".ReadCOA(GetParam("fromSkada","")),0,1,'L',0);
        $this->Cell(130,5,"CURRENCY: ".GetParam("curr",""),0,0,'L',0);
        $this->Cell(130,5,$page." ".$this->PageNo(),0,1,'R',0);
//      $this->Line(10,40,270,40);
        $this->Ln();
//      $this->Ln();
        //Header

        //Column widths
        $w=array(20,80,10,30,30,30,30,30);
        $this->SetFont('Arial','',8);
        $this->Cell($w[0],5,"KTRASL",1,0,'C',0);
        $this->Cell($w[1],5,"DEBTUR",1,0,'C',0);
        $this->Cell($w[2],5,"CR",1,0,'C',0);
        $this->Cell($w[3],5,"CURRENT",1,0,'C',0);
        $this->Cell($w[4],5,"1-30",1,0,'C',0);
        $this->Cell($w[5],5,"31-60",1,0,'C',0);
        $this->Cell($w[6],5,">60",1,0,'C',0);
        $this->Cell($w[7],5,"TOTAL",1,0,'C',0);
        $this->Ln();

    }

    function GridTable($MyResult) {
        //Column widths
        $w=array(20,80,10,30,30,30,30,30);

        //Data
        foreach($MyResult as $row)
        {
            $row_total  = $row["jumlahcurr"]+$row["jumlah30"]+$row["jumlah60"]+$row["jumlah60g"];
            $this->Cell($w[0],5,GetParam("office",""),'LR');
            $this->Cell($w[1],5,$row["debtur"].' - '.$row["debnam"],'LR');
            $this->Cell($w[2],5,$row["top"],'LR',0,'R');
            $this->Cell($w[3],5,number_format($row["jumlahcurr"],2),'LR',0,'R');
            $this->Cell($w[4],5,number_format($row["jumlah30"],2),'LR',0,'R');
            $this->Cell($w[5],5,number_format($row["jumlah60"],2),'LR',0,'R');
            $this->Cell($w[6],5,number_format($row["jumlah60g"],2),'LR',0,'R');
            $this->Cell($w[7],5,number_format($row_total,2),'LR',0,'R');
            $this->Ln();

            $total_cur  = $total_cur + $row["jumlahcurr"];
            $total_30   = $total_30 + $row["jumlah30"];
            $total_60   = $total_60 + $row["jumlah60"];
            $total_60g  = $total_60g + $row["jumlah60g"];
        }
        $total_row  = $total_cur + $total_30 + $total_60 + $total_60g;
        $this->Cell($w[0]+$w[1]+$w[2],5,"TOTAL:",1,0,'R',0);
        $this->Cell($w[3],5,number_format($total_cur,2),1,0,'R',0);
        $this->Cell($w[4],5,number_format($total_30,2),1,0,'R',0);
        $this->Cell($w[5],5,number_format($total_60,2),1,0,'R',0);
            $this->Cell($w[6],5,number_format($total_60g,2),1,0,'R',0);
            $this->Cell($w[7],5,number_format($total_row,2),1,0,'R',0);
        $this->Ln();
        $this->Cell($w[0]+$w[1]+$w[2],5,"PERCENTAGE:",1,0,'R',0);
        $this->Cell($w[3],5,number_format($total_cur/$total_row*100,2)."%",1,0,'R',0);
        $this->Cell($w[4],5,number_format($total_30/$total_row*100,2)."%",1,0,'R',0);
        $this->Cell($w[5],5,number_format($total_60/$total_row*100,2)."%",1,0,'R',0);
            $this->Cell($w[6],5,number_format($total_60g/$total_row*100,2)."%",1,0,'R',0);
            $this->Cell($w[7],5,number_format($total_row/$total_row*100,2)."%",1,0,'R',0);
        $this->Ln();
        $this->LatestPage=true;

        //Closure line
    }

    function Footer() {
        global $DBConnection;
        $w=array(100,60,100);
        //Closure line
        $this->Cell(260,7,'','T');
        if ($this->LatestPage) {
            $this->Ln();
                $this->Cell($w[0],5,"MENYETUJUI",0,0,'C',0);
                $this->Cell($w[1],5,"",0);
                $this->Cell($w[2],5,"MEMBUAT",0,0,'C',0);
                $this->Ln();$this->Ln();
                $this->Ln();$this->Ln();
            $SQL = "SELECT * FROM $DBName.tblprofile WHERE ktrasl='".GetOfficeID()."'";
            $Result = $DBConnection->dbc->get_row($SQL);

                $this->Cell($w[0],5,"( ".$Result->ttdagp1." )",0,0,'C',0);
                $this->Cell($w[1],5,"",0);
                $this->Cell($w[2],5,"( ".$Result->ttdagp2." )",0,0,'C',0);

        }
    }

}


?>
